﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Odbc;
using System.Linq;
using System.Text;

namespace MachineBuildingSystem
{
    public class DB2Helper
    {
        private string m_ConnectString = "";
        private static DB2Helper Instance = null;
        private DB2Helper()
        {
            string connectString = ConfigurationManager.AppSettings["DB2ConnectionStr"];
            foreach (string item in connectString.Split(';'))
            {
                if (item.StartsWith("PWD="))
                    m_ConnectString += "PWD=" + CommonHelper.Decrypt(item.Substring("PWD=".Length), "tp_ltscm");
                else
                    m_ConnectString += item;
                m_ConnectString += ";";
            }
            m_ConnectString = m_ConnectString.TrimEnd(';');
        }

        public static DB2Helper CreateInstance()
        {
            if (Instance == null)
            {
                Instance = new DB2Helper();
            }
            return Instance;
        }

        public bool DoDB2Execute(string[] sqlArray)
        {
            OdbcConnection conn = null;
            OdbcCommand command = null;

            try
            {
                conn = new OdbcConnection(m_ConnectString);
                conn.Open();
                OdbcTransaction transaction = conn.BeginTransaction();

                command = new OdbcCommand();
                command.Connection = conn;
                command.Transaction = transaction;
                for (int i = 0; i < sqlArray.Length; i++)
                {
                    command.CommandText = sqlArray[i];
                    command.ExecuteNonQuery();
                }
                transaction.Commit();
            }
            catch (Exception ex)
            {
                string str = "批量执行异常：" + ex.Message;
                LogHelper.error(str);
                throw;
            }
            finally
            {
                conn.Close();
            }
            return true;
        }

        public DataTable DoDB2Select(string sql)
        {
            DataTable retDT = new DataTable();
            DataSet mydataSet = new DataSet();

            OdbcConnection conn = null;
            OdbcDataAdapter adapter = null;
            try
            {
                conn = new OdbcConnection(m_ConnectString);
                adapter = new OdbcDataAdapter();
                adapter.SelectCommand = new OdbcCommand(sql, conn);
                adapter.Fill(mydataSet);
            }
            catch (Exception ex)
            {
                string str = "执行SQL：" + sql + "，数据错误：" + ex.Message;
                LogHelper.error(str);
                throw;
            }
            finally
            {
                conn.Dispose();
                adapter.Dispose();
            }
            if (mydataSet.Tables.Count > 0)
                retDT = mydataSet.Tables[0];
            return retDT;
        }

        //同步
        public bool DoDB2ExecuteBySync(List<UserData> list, out  List<Dictionary<string, object>> syncResultList)
        {
            OdbcConnection conn = null;
            OdbcCommand command = null;
            syncResultList = new List<Dictionary<string, object>>();
            try
            {
                conn = new OdbcConnection(m_ConnectString);
                conn.Open();
                command = new OdbcCommand();
                command.Connection = conn;

                foreach (UserData user in list)
                {
                    Dictionary<string, object> tempDic = new Dictionary<string, object>();
                    tempDic.Add("type", "user");
                    tempDic.Add("id", user.id.ToString());
                    string sql = "";
                    if (user.type == 0)
                    {
                        //判断是否存在
                        sql = "select * from HS_SECU_USER where ID = " + user.id;
                        DataTable dt = DoDB2Select(sql);
                        if (dt == null || dt.Rows.Count == 0)
                            sql = "insert into HS_SECU_USER (ID, USERNAME, PASSWORD, EMP_ID, EMP_NAME) values ("
                                + user.id + ",'" + user.username + "','" + user.password + "'," + user.employeeId + ",'" + user.employeeName + "')";
                        else
                            sql = "update HS_SECU_USER set USERNAME = '" + user.username + "', PASSWORD = '"
                                + user.password + "', EMP_ID = " + user.employeeId + ", EMP_NAME = '" + user.employeeName + "' where ID = " + user.id;
                    }
                    else if (user.type == 1)
                    {
                        //判断是否存在
                        sql = "select * from HS_SECU_USER where ID = " + user.id;
                        DataTable dt = DoDB2Select(sql);
                        if (dt == null || dt.Rows.Count == 0)
                            sql = "insert into HS_SECU_USER (ID, USERNAME, PASSWORD, EMP_ID, EMP_NAME) values ("
                                + user.id + ",'" + user.username + "','" + user.password + "'," + user.employeeId + ",'" + user.employeeName + "')";
                        else
                            sql = "update HS_SECU_USER set USERNAME = '" + user.username + "', PASSWORD = '"
                                + user.password + "', EMP_ID = " + user.employeeId + ", EMP_NAME = '" + user.employeeName + "' where ID = " + user.id;
                    }
                    else if (user.type == 2)
                        sql = "delte from where ID = " + user.id;
                    tempDic.Add("syncType", user.type);

                    try
                    {
                        command.CommandText = sql;
                        command.ExecuteNonQuery();
                        tempDic.Add("syncResult", "ok");
                    }
                    catch (Exception ex)
                    {
                        tempDic.Add("syncResult", "failed");
                        tempDic.Add("cause", ex.Message);
                        LogHelper.error("同步：" + user.username + ",类型为：" + user.type + "时异常：" + ex.Message);
                    }
                    syncResultList.Add(tempDic);
                }
            }
            catch (Exception ex)
            {
                string str = "批量执行异常：" + ex.Message;
                LogHelper.error(str);
                return false;
            }
            finally
            {
                conn.Close();
            }
            return true;
        }
    }
}
